import random
from faker import Faker
import pymysql

# 数据库配置
db = pymysql.connect(host="127.0.0.1",
                        port=3306,
                        user="root",
                        password="secret",
                        db="db_homework",
                        charset="utf8")
# 获取操作游标
cursor = db.cursor()

def run_sql(sql, search = False):
    try:
        # 执行sql语句
        cursor.execute(sql)
        result = None
        if search:
            # 返回结果
            result = cursor.fetchall()
        else:
            # 执行sql语句
            db.commit()
        
        return result
    except Exception as e:
        # 发生错误时回滚
        print('sql语句错了：', sql, e)
        db.rollback()
        return None

fake = Faker('zh_CN')
# 先插入课程
courses = ['摸鱼学导论', '摸鱼学实战', '计算机系统结构', '数据库系统原理', '量子力学',
            '概率论与数理统计', '资本论', '操作系统']

for name in courses:
    sql = "insert into c" \
            "(cname, credit) value ('{}', {})".format(name, random.randint(1, 5))
    run_sql(sql)

# 拿一下有多少课
count_course = run_sql('SELECT COUNT(*) FROM c', True)[0][0]
print('目前课程数：', count_course)

# 新建一些学生
for x in range(15):
    gender = '男'
    name = ''
    if random.random() >= 0.5:
        gender = '女'
        name = fake.unique.name_female()
    else:
        name = fake.unique.name_male()
    sno = str(201900000 + fake.unique.random_int(min=1, max=99999)) # 学号
    student = [
        sno,
        name,
        gender,
        random.randint(18, 22),
        random.choice(['CS', 'MA', 'IS', 'PY', 'JV', 'JS'])
    ]
    # 插入学生表
    sql = "insert into s" \
            "(sno, sname, sex, age, dept) value ('{}', '{}', '{}', {}, '{}')".format(*student)
    run_sql(sql)

    # 随机选1-3门课
    for cid in random.sample(range(1, count_course + 1), random.randint(1, 3)):
        sql = "insert into sc" \
            "(sno, cno, grade) value ('{}', {}, {})".format(sno, cid, random.randint(55, 100))
        run_sql(sql)
